/*
 * Created on Feb 9, 2006
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
package com.hezhi.framework.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.sqlite.SQLiteConfig;
import org.sqlite.SQLiteDataSource;

/**
 * SQLite operator class
 *         
 */
public class SQLiteDAO {
	
	public Connection getConnection() throws SQLException {
		SQLiteConfig config = new SQLiteConfig();
		// config.setReadOnly(true);
		config.setSharedCache(true);
		config.enableRecursiveTriggers(true);

		SQLiteDataSource ds = new SQLiteDataSource(config);
		ds.setUrl("jdbc:sqlite:configuration/sample.db");
		return ds.getConnection();
	}

	// create Table
	public void createTable(Connection con) throws SQLException {
		//String sql = "DROP TABLE IF EXISTS users ;create table users (username string, password string,right string,filepath string); ";
		String sql = "create table IF NOT EXISTS users (username string, password string,privilege string,filepath string); ";
		Statement stat = null;
		stat = con.createStatement();
		int i = stat.executeUpdate(sql);
		System.out.println(i);
	}

	// drop table
	public void dropTable(Connection con) throws SQLException {
		String sql = "drop table users ";
		Statement stat = null;
		stat = con.createStatement();
		stat.executeUpdate(sql);
	}

	// 新增
	public void insert(Connection con, int id, String name) throws SQLException {
		String sql = "insert into users (username,password,privilege,filepath) values(?,?,?,?)";
		PreparedStatement pst = null;
		pst = con.prepareStatement(sql);
		int idx = 1;
		pst.setInt(idx++, id);
		pst.setString(idx++, name);
		pst.executeUpdate();

	}

	// 修改
	public void update(Connection con, int id, String username) throws SQLException {
		String sql = "update users set name = ? where username = ?";
		PreparedStatement pst = null;
		pst = con.prepareStatement(sql);
		int idx = 1;
		pst.setString(idx++, username);
		pst.setString(idx++, username);
		pst.executeUpdate();
	}

	// 刪除
	public void delete(Connection con, int id) throws SQLException {
		String sql = "delete from users where username = ?";
		PreparedStatement pst = null;
		pst = con.prepareStatement(sql);
		int idx = 1;
		pst.setInt(idx++, id);
		pst.executeUpdate();
	}

	public void selectAll(Connection con) throws SQLException {
		String sql = "select * from users";
		Statement stat = null;
		ResultSet rs = null;
		stat = con.createStatement();
		rs = stat.executeQuery(sql);
		while (rs.next()) {
			System.out.println(rs.getString("username") + "\t" + rs.getString("filepath"));
		}
	}

	public static void main(String args[]) throws SQLException {
		SQLiteDAO test = new SQLiteDAO();
		Connection con = test.getConnection();
		// 建立table
		test.createTable(con);
//		// 新增資料
//		test.insert(con, 1, "第一個");
//		test.insert(con, 2, "第二個");
//		// 查詢顯示資料
//		System.out.println("新增二筆資料後狀況:");
//		test.selectAll(con);
//
//		// 修改資料
//		System.out.println("修改第一筆資料後狀況:");
//		test.update(con, 1, "這個值被改變了!");
//		// 查詢顯示資料
//		test.selectAll(con);
//
//		// 刪除資料
//		System.out.println("刪除第一筆資料後狀況:");
//		test.delete(con, 1);
		// 查詢顯示資料
		test.selectAll(con);

		// 刪除table
		//test.dropTable(con);

		con.close();

	}
}
